﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Model;
using Automobile.service.Common;
using System.Data.SqlClient;
using System.Data;

namespace Automobile.service.DAL
{
    public class SupplierDAL
    {
        SQLHelper h = new SQLHelper();
        /// <summary>
        /// 显示供应商列表
        /// </summary>
        /// <returns></returns>
        public List<Supplier> ShowSupplier()
        {
            string sql = "select Supplier_Id,Supplier_Name,Supplier_People,Supplier_Phone,Supplier_Addr from Supplier";
            List<Supplier> list = new List<Supplier>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Supplier s = new Supplier();
                s.Supplier_Id = Convert.ToInt32(item["Supplier_Id"]);
                s.Supplier_Name = item["Supplier_Name"].ToString();
                s.Supplier_People = item["Supplier_People"].ToString();
                s.Supplier_Phone = item["Supplier_Phone"].ToString();
                s.Supplier_Addr = item["Supplier_Addr"].ToString();
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 添加供应商
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int AddSupplier(Supplier s)
        {
            string ProcName = "AddSupplier";
            SqlParameter[] par = new SqlParameter[]
            {
                new SqlParameter("@Supplier_Name",s.Supplier_Name),
                new SqlParameter("@Supplier_People",s.Supplier_People),
                new SqlParameter("@Supplier_Phone",s.Supplier_Phone),
                new SqlParameter("@Supplier_Addr",s.Supplier_Addr)
            };
            return h.ExecNonquery_Proc(ProcName,par);
        }
        /// <summary>
        /// 信息数量
        /// </summary>
        /// <returns></returns>
        public int CountSupplier()
        {
            string sql = "select count(*) from Supplier";
            return Convert.ToInt32( h.ExecScalar(sql));
        }
        /// <summary>
        /// 根据Id的获取数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable SuppLier_Hu(int id)
        {
            string sql = "select Supplier_Id,Supplier_Name,Supplier_People,Supplier_Phone,Supplier_Addr from Supplier where Supplier_Id=" + id + "";
             return  h.ExecDataTable(sql);
            
        }
        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int UptSupplier(Supplier s)
        {
            string ProcName = "UptSupplier";
            SqlParameter[] par = new SqlParameter[]
            {
                new SqlParameter("@Supplier_Name",s.Supplier_Name),
                new SqlParameter("@Supplier_People",s.Supplier_People),
                new SqlParameter("@Supplier_Phone",s.Supplier_Phone),
                new SqlParameter("@Supplier_Addr",s.Supplier_Addr),
                 new SqlParameter("@Supplier_Id",s.Supplier_Id),
            };
            return h.ExecNonquery_Proc(ProcName, par);
        }
        /// <summary>
        /// 显示供应商列表
        /// </summary>
        /// <returns></returns>
        public List<Supplier> ShowSupplier_Sel(string Supplier_Name)
        {
            string sql = "select Supplier_Id,Supplier_Name,Supplier_People,Supplier_Phone,Supplier_Addr from Supplier where 1=1";
            if(!string.IsNullOrEmpty(Supplier_Name))
            {
                sql += "and Supplier_Name like '%" + Supplier_Name + "%'";
            }
            List<Supplier> list = new List<Supplier>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Supplier s = new Supplier();
                s.Supplier_Id = Convert.ToInt32(item["Supplier_Id"]);
                s.Supplier_Name = item["Supplier_Name"].ToString();
                s.Supplier_People = item["Supplier_People"].ToString();
                s.Supplier_Phone = item["Supplier_Phone"].ToString();
                s.Supplier_Addr = item["Supplier_Addr"].ToString();
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 删除供应商
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Del(int id)
        {
            string sql = "delete Supplier where Supplier_Id=" + id + "";
            return h.ExecNonquery(sql);
        }
        public int count()
        {
            string sql = "select count(*) from Supplier";
            return Convert.ToInt32(h.ExecScalar(sql));
        }

    }
}
